package com.railway.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.railway.bean.Order;
import com.railway.bean.Orderdetailed;
import com.railway.dao.OrderDao;
import com.railway.util.DBUtil;

public class OrderDaoImpl implements OrderDao {

	JdbcTemplate jt = new JdbcTemplate(DBUtil.getDataSource());
	@Override
	public List<Order> selectOrdetId() {
		String sql = "select order_id from `order`";
		return jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class));
	}
	@Override
	public int insertOrder(Order order) {
		String sql = "insert into `order`(order_id,user_id,train_id,train_date,train_date,"
				+ "starting_station_id,terminal_station_id,order_price,created_at,use_Integra)"
				+ " values(?,?,?,?,?,?,?,?,?,?)";
		return jt.update(sql,order.getOrder_id(),order.getUser_id(),order.getTrain_id(),order.getTrain_date(),
				order.getStatting_station_id(),order.getTerminals_tation_id(),order.getOrder_price(),
				order.getCreated_at(),order.getCreated_at());
	}
	@Override
	public int selectOrdeTailedId(int order_id) {
		String sql = "select conut(1) from orderdetailed where order_id = ? grop by oder_detailed_id";
		return (jt.query(sql, new BeanPropertyRowMapper<Integer>(Integer.class),order_id)).get(0);
	}
	@Override
	public List<Order> selectOrders(int train_id) {
		String sql = "select * from `order`";
		return jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),train_id);
	}
	@Override
	public List<Orderdetailed> selectOrdeTailed(int order_id) {
		String sql = "select * from orderdetailed where order_id = ? ";
		return jt.query(sql, new BeanPropertyRowMapper<Orderdetailed>(Orderdetailed.class),order_id);
	}
	@Override
	public int insertOrderTailed(Orderdetailed o) {
		String sql = "insert into orderdetailed(order_detailed_id,order_id,passenger_name,passenger_id_type,"
				+ "passenger_id_num,passenger_type,seat_type,camage_num,seat_num,seat_localtion) "
				+ "values(?,?,?,?,?,?,?,?,?,?)";
		return jt.update(sql,o.getOder_detailed_id(),o.getOrder_id(),o.getPassenger_name(),o.getPassenger_id_type(),o.getPassenger_id_num(),
				o.getPassenger_type(),o.getSeat_type(),o.getCamage_num(),o.getSeat_num(),o.getSeat_location());
	}
	@Override
	public Order selectOrder(int order_id) {
		String sql = "SELECT t1.* FROM `order` t1 JOIN station t2 ON t1.starting_station_id = t2.station_id JOIN station t3 ON t3.station_id "
				+ "WHERE t1.order_id = ?;";
		return (jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),order_id)).get(0);
	}
	@Override
	public int updateOrderIsPaid(int order_id) {
		String sql = "Update `order` set is_paid = 1 where order_id = ?";
		return jt.update(sql,order_id);
	}
	@Override
	public List<Order> slectOrderByUserId(int user_id) {
		String sql = "select * from `order` where user_id = ? ";
		return jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
	}
	@Override
	public List<Order> selectOrderUnpaidByUserId(int user_id) {
		Date nowDate = new Date();
		String sql = "select * from `order` where user_id = ? and is_exit = 1 and is_paid = 0";
		List<Order> li = jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
		List<Order> orders = new ArrayList<Order>();
		for (Order order : li) {
			if( (nowDate.getTime() - order.getCreated_at().getTime()) < (1000*60*20) ) {
				li.add(order);
			}else {
				sql = "update `order` set is_exit = 0 where order_id = ?";
				jt.update(sql,order.getOrder_id());
			}
		}
		return orders;
	}
	@Override
	public List<Order> selectOrderUntravelByUserId(int user_id) {
		Date nowDate = new Date();
		
		Date beforeDate = new Date(nowDate.getTime()-30*24*60*60*1000);
		String sql = "select * from `order` where user_id = ? and is_exit = 1 and is_paid = 1 and beforeDate < train_date < nowDate";
		
		List<Order> orders = jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
		
		for (Order order : orders) {
			order.setOrderdetaileds(selectOrdeTailed(order.getOrder_id()));
		}
		
		return orders;
	}
	
	@Override
	public List<Order> selectOrderAllByUserId(int user_id) {
		String sql = "select * from `order` where user_id and is_exit = 1 and user_id = ? beforeDate < train_date < nowDate";
		List<Order> orders = jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
		
		for (Order order : orders) {
			order.setOrderdetaileds(selectOrdeTailed(order.getOrder_id()));
		}
		
		return orders;
	}
	
	@Override
	public List<Order> selectOrderticketchangingByUserId(int user_id) {
		String sql = "select * from `order` where user_id and is_exit = 1 and is_paid = 1 and user_id = ? and is_ticket_changing = 0 and train_date < date";
		List<Order> orders = jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
		
		for (Order order : orders) {
			order.setOrderdetaileds(selectOrdeTailed(order.getOrder_id()));
		}
		return orders;
	}
	@Override
	public List<Order> selectOrderRefundByUserId(int user_id) {
		Date date = new Date((new Date()).getTime()-10*60*1000);
		
		String sql = "select * from `order` where user_id and is_exit = 1 and is_paid = 1 and user_id = ? and train_date < date";
		List<Order> orders = jt.query(sql, new BeanPropertyRowMapper<Order>(Order.class),user_id);
		
		for (Order order : orders) {
			order.setOrderdetaileds(selectOrdeTailed(order.getOrder_id()));
		}
		return orders;
	}



}
